/* Copyright (c) 2012 Ronan Mac an tSaoir

	 Licensed under the Apache License, Version 2.0 (the "License");
	 you may not use this file except in compliance with the License.
	 You may obtain a copy of the License at

		 http://www.apache.org/licenses/LICENSE-2.0

	 Unless required by applicable law or agreed to in writing, software
	 distributed under the License is distributed on an "AS IS" BASIS,
	 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
	 See the License for the specific language governing permissions and
	 limitations under the License.
*/

package com.enjoyireland.hiking;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.net.Uri;

/**
 * Helper Class for Walks DB interaction
 * 
 * @author Rónan Mac an tSaoir
 *
 */
public class WalkDBHelper extends SQLiteOpenHelper implements Constants{
 
    private SQLiteDatabase _walkDB; 
    private final Context _context;
 
    public WalkDBHelper(Context context) {
    	super(context, DB_NAME, null, 1);
        this._context = context;
    }	
 
	/**
	 * Install walks DB on SD card
	 *
	 */
    public void installDB() throws IOException{
    	/* only install if necessary */
    	boolean exists = dbExists();
    	if(!exists){
	    	this.getReadableDatabase();
	    	try {
				copyDB();
			} catch (IOException e) {
	    		throw new Error("Error copying database");
	    	}
    	}
    }

    /**
     * Check if DB exists
     */
    private boolean dbExists(){
    	_walkDB = null;
    	try{
    		String path = DB_PATH + DB_NAME;
    		_walkDB = SQLiteDatabase.openDatabase(path, null, SQLiteDatabase.OPEN_READONLY);
    	}catch(SQLiteException e){
    		// not a problem... install DB in this case
    	}
 
    	/* close DB if opened successfully */
    	if(_walkDB != null){
    		_walkDB.close();
    	}
 
    	return _walkDB != null ? true : false;
    }
 
    /**
     * Copy DB from assets to SD card
     * */
    private void copyDB() throws IOException{
    	/* put assets db in stream */
    	InputStream in = _context.getAssets().open("data/"+DB_NAME);
 
    	/* write to sd card */
    	String outFileName = DB_PATH + DB_NAME;
    	OutputStream out = new FileOutputStream(outFileName);
    	byte[] buffer = new byte[1024];
    	int length;
    	while ((length = in.read(buffer))>0){
    		out.write(buffer, 0, length);
    	}
 
    	/* tidy up */
    	out.flush();
    	out.close();
    	in.close();
    }
 
    public void openDB() throws SQLException{
    	String myPath = DB_PATH + DB_NAME;
    	_walkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
    }
 
    @Override
	public synchronized void close() {
    	if(_walkDB != null)
    		_walkDB.close();
    	super.close();
	}
 
	@Override
	public void onCreate(SQLiteDatabase db) {
		// not needed
	}
 
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		_context.deleteDatabase(DB_NAME);
		try {
			installDB();
		} catch (IOException e) {
		}
	}
 
	/**
	 * Utility method: return walk names from specified database
	 * @param db
	 * 		database to search through for walk names
	 * @return Cursor
	 */
    public Cursor getWalkNames(SQLiteDatabase db){
    	return db.query(TABLE_NAME, FROM, null, null, null, null, SORT_BY_NAME);
    }
    
	/**
	 * Utility method: return walks from db, sorted by specified column name
	 * @param db
	 * 		database to search through for walk names
	 * @param column
	 * 		column name by which to sort walks
	 * @return Cursor
	 */
    public Cursor getSortedWalks(SQLiteDatabase db, String column){
    	return db.query(TABLE_NAME, FROM, null, null, null, null, column);
    }
    
    /**
	 * Utility method: return grid reference data / gpx file names for specified walk, from specified database
	 * @param db
	 * 		database to search through
	 * @param walkName
	 * 		name of walk to search for
	 * @return Cursor
	 */
    public String[] getGPSData(SQLiteDatabase walkDB, Uri walkUri){
    	SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
	    queryBuilder.setTables(WalkDBHelper.TABLE_NAME);
	    
	    String walkID = walkUri.getLastPathSegment();
		
		String[] data = new String[2];
		Cursor c = walkDB.query(TABLE_NAME, new String[] {ID,COL_GRID,COL_GPX}, "_id = "+walkID, null, null, null, null);
		c.moveToFirst();
		
		data[0] = c.getString(1);
		data[1] = c.getString(2);
		c.close();
		return data;
    }
    
    /**
     * Utility method: mark walk as viewed
     * 
     */
    public void viewedWalk(SQLiteDatabase db,Uri walkUri){
    	String walkID = walkUri.getLastPathSegment();
    	ContentValues args = new ContentValues();
    	args.put(COL_VIEWED, "true");
    	db.update(TABLE_NAME, args, "_id = "+walkID, null);
    }

    /**
	 * Utility method: return string [] containing walk details
	 * @param db
	 * 		database to search through
	 * @param walkUri
	 * 		Uri specifying ID of walk
	 * @return
	 */
	public String[] getWalkDetails(SQLiteDatabase walkDB, Uri walkUri) {
		
		SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
	    queryBuilder.setTables(WalkDBHelper.TABLE_NAME);
	    
	    String walkID = walkUri.getLastPathSegment();
		
		String[] details = new String[9];
		Cursor c = walkDB.query(TABLE_NAME, FROM_DETAILS, "_id = "+walkID, null, null, null, null);
		c.moveToFirst();
		
		details[COL_NAME_INDEX] 	= c.getString(COL_NAME_INDEX);
		details[COL_LEN_INDEX] 		= String.valueOf(c.getInt(COL_LEN_INDEX));
		details[COL_GRID_INDEX] 	= c.getString(COL_GRID_INDEX);
		details[COL_DIFF_INDEX] 	= String.valueOf(c.getInt(COL_DIFF_INDEX));
		details[COL_ID_INDEX] 		= c.getString(COL_ID_INDEX);
		details[COL_DESC_INDEX] 	= c.getString(COL_DESC_INDEX);
		details[COL_EQUIP_INDEX] 	= c.getString(COL_EQUIP_INDEX);
		details[COL_TERRAIN_INDEX] 	= c.getString(COL_TERRAIN_INDEX);
		details[COL_CO_INDEX] 		= c.getString(COL_CO_INDEX);
		
		c.close();
		
		return details;
	}
}